package Mysql;

import instruction.*;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;

public class Database {

    private static String url = "jdbc:mysql://localhost:3306/table?useSSL=false";//table是表的名字;
    private static String user = "root";
    private static String password = "123456";
    private static Connection con = null;
    private static PreparedStatement ptmt = null;
    private static Statement statement = null;
    private static boolean flag;

    //连接数据库
    public static void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            con = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("数据库连接错误");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //释放数据库
    private static void release(Connection connection, PreparedStatement preparedStatement) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (preparedStatement != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库回收资源错误");
        }
    }

    //登录数据库库
    public static boolean login(Login login) throws SQLException {
        flag = false;
        connect();
        try {
            String sql = "select user_account,user_password from user_table where user_account = ? and user_password = ?";
            ptmt = con.prepareStatement(sql);
            ptmt.setString(1, login.getAccount());
            ptmt.setString(2, login.getPassword());
            ptmt.execute();//执行查询
            ResultSet rs = ptmt.executeQuery();
            if (rs.next()) {
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("查询数据库错误");
        } finally {
            release(con, ptmt);
        }
        return flag;
    }

    //注册数据库
    public static void register(User user) throws SQLException {
        connect();
        try {
            String sql = "insert into user_table (user_name,user_nickname,user_sign,user_sex,user_phone,user_address,user_birthday,user_age,user_head,user_account,user_password,user_email)value(?,?,?,?,?,?,?,?,?,?,?,?)";
            ptmt = con.prepareStatement(sql);
            ptmt.setString(1, user.getName());
            ptmt.setString(2, user.getNickname());
            ptmt.setString(3, user.getSignature());
            ptmt.setString(4, user.getSex());
            ptmt.setString(5, user.getPhone());
            ptmt.setString(6, user.getAddress());
            ptmt.setString(7, user.getBirthday());
            ptmt.setString(8, user.getAge());
            ptmt.setString(9, user.getHead());
            ptmt.setString(10, user.getAccount());
            ptmt.setString(11, user.getPassword());
            ptmt.setString(12, user.getEmail());
            ptmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("插入数据库错误");

        } finally {
            release(con, ptmt);
        }
    }

    //更新数据库
    public static void modify(Modify modify) throws SQLException {
        connect();
        try {
            String sql = "update user_table set user_nickname=?,user_sign=?,user_head=? where user_account=?";
            ptmt = con.prepareStatement(sql);
            ptmt.setString(1,modify.getNickname());
            ptmt.setString(2,modify.getSign());
            ptmt.setString(3,modify.getHead());
            ptmt.setString(4,modify.getAccount());
            ptmt.execute();
        } catch (SQLException e) {
            System.out.println("更新密码数据库错误");
            e.printStackTrace();
        } finally {
            release(con, ptmt);
        }
    }

    public static boolean forget(Forget forget) throws SQLException {
        flag = false;
        connect();
        try {
            String email = forget.getAccount();
            String password = forget.getPassword();
            String sql = "update user_table set user_password= '" + password + "' where user_email = '" + email + "'";
            ptmt = con.prepareStatement(sql);
            ptmt.execute();
            flag = true;
        } catch (SQLException e) {
            System.out.println("更新密码数据库错误");
            e.printStackTrace();
        } finally {
            release(con, ptmt);
        }
        return flag;
    }


    //获取用户
    public static User GetUser(String account) throws SQLException {
        connect();
        User user = null;
        String sql = "select * from user_table where user_account=?";
        ptmt = con.prepareStatement(sql);
        ptmt.setString(1, account);
        ResultSet resultSet = ptmt.executeQuery();
        if (resultSet.next()) {
            String name = resultSet.getString("user_nickname");  //姓名
            String act = resultSet.getString("user_account");  //账号
            String password = resultSet.getString("user_password");  //密码
            String sex = resultSet.getString("user_sex");  //性别
            String birthday = resultSet.getString("user_birthday"); //生日
            String sign = resultSet.getString("user_sign");  //个性签名
            String email = resultSet.getString("user_email");
            String address = resultSet.getString("user_address");
            String nickName = resultSet.getString("user_nickname");
            String phone = resultSet.getString("user_phone");
            String age = resultSet.getString("user_age");
            String head = resultSet.getString("user_head");
            user = new User(name, act, password, sex, birthday, sign, email, age, address, nickName, phone, head);
        }
        return user;
    }

    //添加好友
    public static void AddFriend(Friend friend) throws SQLException {
        connect();
        String sql = "insert into friend_table (user_account,friend_remark,friend_account,friend_image,friend_birthday,friend_sex,friend_age,friend_sign,friend_address,friend_phone,friend_email,friend_name,friend_nickname) value(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        ptmt = con.prepareStatement(sql);
        Member member = friend.getMember();
        String user_account = friend.getUserAccount();
        String friend_remark = friend.getFriendRemarks();
        String friend_account = member.getAccount();
        String friend_image = member.getHead();
        String friend_birthday = member.getBirthday();
        String friend_sex = member.getSex();
        String friend_age = member.getAge();
        String friend_sign = member.getSignature();
        String friend_address = member.getAddress();
        String friend_phone = member.getPhone();
        String friend_email = member.getEmail();
        String friend_name = member.getName();
        String friend_nickname = member.getNickname();
        ptmt.setString(1, user_account);
        ptmt.setString(2, friend_remark);
        ptmt.setString(3, friend_account);
        ptmt.setString(4, friend_image);
        ptmt.setString(5, friend_birthday);
        ptmt.setString(6, friend_sex);
        ptmt.setString(7, friend_age);
        ptmt.setString(8, friend_sign);
        ptmt.setString(9, friend_address);
        ptmt.setString(10, friend_phone);
        ptmt.setString(11, friend_email);
        ptmt.setString(12, friend_name);
        ptmt.setString(13, friend_nickname);
        ptmt.execute();
        release(con, ptmt);
    }

    //获取用户的所有好友的表列
    public static ArrayList<Friend> GetAllFriend(String account) throws SQLException {
        connect();
        ArrayList<Friend> friendList = new ArrayList<>();
        String sql = "select * from friend_table where user_account=?";
        ptmt = con.prepareStatement(sql);
        ptmt.setString(1, account);
        ResultSet resultSet = ptmt.executeQuery();
        while (resultSet.next()) {
            String user_account = resultSet.getString("user_account");
            String friend_remark = resultSet.getString("friend_remark");
            String friend_account = resultSet.getString("friend_account");
            String friend_image = resultSet.getString("friend_image");
            String friend_birthday = resultSet.getString("friend_birthday");
            String friend_sex = resultSet.getString("friend_sex");
            String friend_age = resultSet.getString("friend_age");
            String friend_sign = resultSet.getString("friend_sign");
            String friend_address = resultSet.getString("friend_address");
            String friend_phone = resultSet.getString("friend_phone");
            String friend_email = resultSet.getString("friend_email");
            String friend_name = resultSet.getString("friend_name");
            String friend_nickname = resultSet.getString("friend_nickname");
            Member member = new Member(friend_name, friend_account, friend_sex, friend_birthday, friend_sign, friend_email, friend_age, friend_address, friend_nickname, friend_phone, friend_image);
            Friend friend = new Friend(user_account, friend_remark, member);
            friendList.add(friend);
        }
        release(con, ptmt);
        return friendList;
    }

    //创建群的基本信息
    public static void createGroup(Group group) throws SQLException {
        connect();
        String sql = "insert into group_table (group_account,group_name,group_introduction,group_image,creator_account)value(?,?,?,?,?)";
        ptmt = con.prepareStatement(sql);
        ptmt.setString(1, group.getAccount());
        ptmt.setString(2, group.getName());
        ptmt.setString(3, group.getIntroduction());
        ptmt.setString(4, group.getImage());
        ptmt.setString(5, group.getCreator());
        ptmt.execute();
        release(con, ptmt);
        AddGroup(new GroupMember(group, group.getMemberList().get(0)));
    }

    //加入群聊
    public static void AddGroup(GroupMember groupMember) throws SQLException {
        connect();
        String sql = "insert into member_table(group_account,member_account,member_image,member_name,member_sex,member_age,member_address,member_phone,member_sign,member_nickname,member_email,member_birthday) value(?,?,?,?,?,?,?,?,?,?,?,?)";
        ptmt = con.prepareStatement(sql);
        Member member = groupMember.getMember();
        String groupAccount = groupMember.getGroup().getAccount();
        String account = member.getAccount();
        String name = member.getName();
        String image = member.getHead();
        String sex = member.getSex();
        String sign = member.getSignature();
        String age = member.getAge();
        String phone = member.getPhone();
        String email = member.getEmail();
        String nickname = member.getNickname();
        String address = member.getAddress();
        String birthday = member.getBirthday();
        ptmt.setString(1, groupAccount);
        ptmt.setString(2, account);
        ptmt.setString(3, image);
        ptmt.setString(4, name);
        ptmt.setString(5, sex);
        ptmt.setString(6, age);
        ptmt.setString(7, address);
        ptmt.setString(8, phone);
        ptmt.setString(9, sign);
        ptmt.setString(10, nickname);
        ptmt.setString(11, email);
        ptmt.setString(12, birthday);
        ptmt.execute();
        release(con, ptmt);
    }

    public static ArrayList<Group> GetAllGroup(String account) throws SQLException {
        ArrayList<Group> groupList = new ArrayList<>();
        connect();
        String sql = "select group_account from member_table where member_account=?";
        ptmt = con.prepareStatement(sql);
        ptmt.setString(1, account);
        ResultSet resultSet = ptmt.executeQuery();
        while (resultSet.next()) {
            String GroupAccount = resultSet.getString("group_account");
            Group group = GetGroup(GroupAccount);
            groupList.add(group);
        }
        release(con, ptmt);
        return groupList;

    }

    //获取群（包括群的基本信息和群的成员）
    public static Group GetGroup(String groupAccount) throws SQLException {
        ArrayList<Member> memberList = GetGroupMember(groupAccount);
        connect();
        Group group = null;
        String sql = "select * from group_table where group_account=?";
        ptmt = con.prepareStatement(sql);
        ptmt.setString(1, groupAccount);
        ResultSet resultSet = ptmt.executeQuery();
        while (resultSet.next()) {
            String name = resultSet.getString("group_name");
            String account = resultSet.getString("group_account");
            String image = resultSet.getString("group_image");
            String creator = resultSet.getString("creator_account");
            String introduction = resultSet.getString("group_introduction");
            group = new Group(image, name, account, introduction, creator, memberList);
        }
        release(con, ptmt);
        return group;
    }

    //获取群成员
    public static ArrayList<Member> GetGroupMember(String groupAccount) throws SQLException {
        connect();
        ArrayList<Member> memberList = new ArrayList<>();
        String sql = "select * from member_table where group_account=?";
        ptmt = con.prepareStatement(sql);
        ptmt.setString(1, groupAccount);
        ResultSet resultSet = ptmt.executeQuery();
        while (resultSet.next()) {
            String account = resultSet.getString("member_account");
            String name = resultSet.getString("member_name");
            String image = resultSet.getString("member_image");
            String sex = resultSet.getString("member_sex");
            String sign = resultSet.getString("member_sign");
            String age = resultSet.getString("member_age");
            String phone = resultSet.getString("member_phone");
            String email = resultSet.getString("member_email");
            String nickname = resultSet.getString("member_nickname");
            String address = resultSet.getString("member_address");
            String birthday = resultSet.getString("member_birthday");
            Member member = new Member(name, account, sex, birthday, sign, email, age, address, nickname, phone, image);
            memberList.add(member);
        }
        release(con, ptmt);
        return memberList;
    }

    //存储一般的文字,图片信息
    public static void Record(Record record) throws SQLException, IOException {
        connect();
        String sender_account = record.getSender_account();
        String receiver_account = record.getReceiver_account();
        String content = record.getContent();
        String time = record.getTime();
        String type =record.getType();
        if(type.equals("word")||type.equals("emoticon")||type.equals("file")) {
            String sql = "insert into record_table (message_type,sender_account,receiver_account,content,time)value(?,?,?,?,?)";
            ptmt = con.prepareStatement(sql);
            ptmt.setString(1, type);
            ptmt.setString(2, sender_account);
            ptmt.setString(3, receiver_account);
            ptmt.setString(4, content);
            ptmt.setString(5, time);
            ptmt.execute();
        }else if(type.equals("picture")){
            byte [] picture=record.getPicture();
            InputStream input=new ByteArrayInputStream(picture);
            String sql="insert into record_table(message_type,sender_account,receiver_account,content,time,picture)value(?,?,?,?,?,?)";
            ptmt=con.prepareStatement(sql);
            ptmt.setString(1,type);
            ptmt.setString(2,sender_account);
            ptmt.setString(3,receiver_account);
            ptmt.setString(4,content);
            ptmt.setString(5,time);
            ptmt.setBinaryStream(6,input,(int)input.available());
            ptmt.execute();
        }
        release(con,ptmt);
    }

    //获取群发消息
    public static ArrayList<ArrayList<Message>> GetGroupRecord( ArrayList<Group> groupList) throws SQLException, IOException {
        connect();
        ArrayList<ArrayList<Message>> messageRecord = new ArrayList<>();
        String sql = "select * from record_table where receiver_account=?";
        ptmt = con.prepareStatement(sql);
        for (int i = 0; i < groupList.size(); i++) {
            ArrayList<Message> messageList=new ArrayList<>();
            ptmt.setString(1, groupList.get(i).getAccount());
            ResultSet resultSet = ptmt.executeQuery();
            while (resultSet.next()){
                String type=resultSet.getString("message_type");
                Message message=new Message("groupChat");
                GroupMessage groupMessage=new GroupMessage(type);
                if(type.equals("picture")){
                    InputStream input =resultSet.getBinaryStream("picture");
                    ByteArrayOutputStream bos=new ByteArrayOutputStream();
                    byte[] buf= new byte[100];
                    int temp;
                    while((temp=input.read(buf,0,100))>0) {
                        bos.write(buf, 0, temp);
                    }
                    groupMessage.setPicture(bos.toByteArray());
                }
                String senderAccount = resultSet.getString("sender_account");
                String receiverAccount = resultSet.getString("receiver_account");
                String content = resultSet.getString("content");
                String time = resultSet.getString("time");
                groupMessage.setContent(content);
                groupMessage.setTime(time);
                groupMessage.setReceiver(receiverAccount);
                groupMessage.setSender(senderAccount);
                message.setGroupMessage(groupMessage);
               messageList.add(message);
            }
            messageRecord.add(messageList);
        }
        release(con,ptmt);
        return messageRecord;
    }

    //获取私聊消息
    public static ArrayList<ArrayList<Message>> GetPrivateRecord(ArrayList<Friend> friendList) throws SQLException, IOException {
        connect();
        ArrayList<ArrayList<Message>> messageRecord = new ArrayList<>();
        String sql = "select * from record_table where sender_account=? and receiver_account=? or sender_account=? and receiver_account=? ";
        ptmt = con.prepareStatement(sql);
        for (Friend friend :friendList) {
            ArrayList<Message> messageList=new ArrayList<>();
            ptmt.setString(1,friend.getUserAccount());
            ptmt.setString(2, friend.getMember().getAccount());
            ptmt.setString(3, friend.getMember().getAccount());
            ptmt.setString(4,friend.getUserAccount());
            ResultSet resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                Message message = new Message("privateChat");
                String type=resultSet.getString("message_type");
                String senderAccount = resultSet.getString("sender_account");
                String receiverAccount = resultSet.getString("receiver_account");
                String content = resultSet.getString("content");
                String time = resultSet.getString("time");
                PrivateMessage privateMessage=new PrivateMessage(type);
                if(type.equals("picture")){
                    InputStream input =resultSet.getBinaryStream("picture");
                    ByteArrayOutputStream bos=new ByteArrayOutputStream();
                    byte[] buf= new byte[100];
                    int temp;
                    while((temp=input.read(buf,0,100))>0){
                        bos.write(buf,0,temp);
                    }
                    privateMessage.setPicture(bos.toByteArray());
                }
                privateMessage.setContent(content);
                privateMessage.setReceiveAccount(receiverAccount);
                privateMessage.setTime(time);
                privateMessage.setSendAccount(senderAccount);
                message.setPrivateMessage(privateMessage);
                messageList.add(message);
            }
            messageRecord.add(messageList);
        }
        release(con,ptmt);
        return messageRecord;
    }
    public void recordImage(){
        connect();
    }
}
